package com.basis.handler.service;

import com.basis.utils.CodeGenerateRuleUtil;
import com.basis.utils.CodeNumberFactory;
import com.basis.utils.StringUtil;
import com.mes.data.EmesDataImport;
import com.mes.data.EmesDataImportColumn;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.hibernate.Query;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@SuppressWarnings("unchecked")
public class DataImportService extends DataImportInterface {
	protected static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式
	protected static SimpleDateFormat dateSdf = new SimpleDateFormat("yyyy-MM-dd");//处理时间格式
	protected String tableName = null;
	protected String clientId = null;
	protected String orgId = null;
	protected String userId = null;
	
	protected Map<String, DataColumn> columnMap = null;
	protected Map<Integer, String> titleMap = null;
			
	
	public void setEmesDataImport(EmesDataImport dataImport) {
		setColumnMap(dataImport);
	}

	public JSONObject importData(InputStream is, String fileName) throws Exception {
		JSONObject result = new JSONObject();
		try {
			Workbook wb = null;
			if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			if (wb != null) {
				//设置标题与列的对应关系
				setTitleMap(wb);
				
				result = this.readExcel(wb);
				if (!result.getBoolean("result")) {
					OBDal.getInstance().rollbackAndClose();
					return result;
				} else {
					OBDal.getInstance().commitAndClose();
					result.put("result", true);
					result.put("msg", "导入成功");
					return result;
				}
			} else {
				result.put("msg", "文件格式错误，只允许Excel的导入");
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			if (e.getClass().getName().contains("ConstraintViolationException")) {
				result.put("result", false);
				result.put("msg", "文件数据有问题，请仔细检查。\n" + e.getMessage());
			} else {
				throw new Exception(e);
			}
		} finally {
			is.close();
		}
		return result;
	}
	
	/**
	 * 通过编号查询模版名称对应字段
	 */
	protected void setColumnMap(EmesDataImport dataImport) {
		tableName = dataImport.getTable().getDBTableName();
		
		OBQuery<EmesDataImportColumn> query = OBDal.getInstance().createQuery(EmesDataImportColumn.class, "emesDataImport = :dataImport");
		query.setNamedParameter("dataImport", dataImport);
		List<EmesDataImportColumn> list = query.list();
		
		columnMap = new HashMap<String, DataColumn>();
		DataColumn dc = null;
		
		for (EmesDataImportColumn column : list) {
			dc = new DataColumn();
			dc.setName(column.getColumn().getDBColumnName());
			dc.setNotNull(column.isNotNull());
			dc.setUnique(column.isUnique());
			dc.setIgnore(column.isUnique());
			
			if (column.getColumn().getReferenceSearchKey() != null) {
				dc.setReferenceId(column.getColumn().getReferenceSearchKey().getId());
			}
			dc.setTypeId(column.getColumn().getReference().getId());
			dc.setWhereSql(column.getWhereSql());
			dc.setErrorMsg(column.getErrorMsg());

			columnMap.put(column.getName(), dc);
		}

		userId = SessionInfo.getUserId();
		User user = OBDal.getInstance().get(User.class, userId);
		clientId = user.getClient().getId();
		orgId = user.getOrganization().getId();
	}
	
	protected void setTitleMap(Workbook wb) {
		if (wb.getNumberOfSheets() == 0) {
			throw new RuntimeException("文件格式错误。请仔细检查！");
		}
		// 读取第一页
		Sheet sheet = wb.getSheetAt(0);
		
		titleMap = new HashMap<Integer, String>();
		Row row = sheet.getRow(1);//直接取第二行为表头行
		Cell cell = null;
		String value = null;
		
		int readLine = 0;
		int columnSize = row.getLastCellNum();
		for (int i = 0; i < columnSize; i++) {
			cell = row.getCell(i);
			value = readValue(cell) + "";
			if (value != null && !"".equals(value)) {
				titleMap.put(i, value);
				
				if (columnMap.containsKey(value)) {
					readLine ++;
				}
			}
		}
		if (readLine == 0) {
			throw new RuntimeException("请检查模板是否正确，第一行为文件标题，第二行未列标题！");
		}
	}
	/**
	 * 读取excel
	 */
	protected JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "";
		JSONObject result = new JSONObject();
		result.put("result", true);
		// 读取第一页
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 2; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			this.saveData(row, i + 1);
		}
		if (!"".equals(meg)) {
			result.put("result", false);
			result.put("msg", meg);
		}
		return result;
	}
	/**
	 * 开始存储记录
	 */
	protected void saveData(Row row, int rowIndex) throws Exception {
		Object colValue = null;
		Object value = null;
		DataColumn column = null;
		String idName = tableName.toUpperCase() + "_ID";
		
		StringBuilder sql = new StringBuilder("insert into ");
		sql.append(tableName).append("(");
		sql.append("AD_Client_ID,Created,CreatedBy,Updated,UpdatedBy");
		
		StringBuilder sqlValue = new StringBuilder(") values ('");
		sqlValue.append(clientId).append("'");
		sqlValue.append(",now(),'").append(userId).append("'");
		sqlValue.append(",now(),'").append(userId).append("'");
		
		List<Object> values = new ArrayList<Object>();
		boolean isProduct = "M_PRODUCT_ID".equals(idName);
		String idValue = StringUtil.getUUID();
		
		boolean orgColumn = false;
		boolean activeColumn = false;
		boolean idColumn = false;
		
		int columnSize = row.getLastCellNum();
		Map<String, Object> valueMap = new HashMap<String, Object>();

		for (int i = 0; i < columnSize; i++) {
			colValue = readValue(row.getCell(i));
			
			valueMap.put(titleMap.get(i), colValue);
		}
		Map<String, String> codes = CodeGenerateRuleUtil.autoGenerateCode(tableName);
		
		for (int i = 0; i < columnSize; i++) {
			column = columnMap.get(titleMap.get(i));
			if (column == null) {
				continue;
			}
			if (codes != null && codes.containsKey(column.getName())) {
				codes.remove(column.getName());
			}
			colValue = valueMap.get(titleMap.get(i));

			if ((colValue != null && !"".equals(colValue)) || column.getWhereSql() != null) {
				if ("AD_ORG_ID".equals(column.getName().toUpperCase())) {
					orgColumn = true;
				}
				if ("ISACTIVE".equals(column.getName().toUpperCase())) {
					activeColumn = true;
				}
				if (idName.equals(column.getName().toUpperCase())) {
					idColumn = true;
				}
				value = getDataValue(colValue, column, valueMap);
				
				if (value != null) {
					//唯一性验证
					if (column.isUnique()) {
						int size = 0;
						try {
							String checkSql = "select count(*) from " + tableName + " where " + column.getName() + " = ?";
							size = Integer.parseInt(getSqlValue(checkSql, value).toString());
						} catch (Exception e) {
							throw new RuntimeException("数据唯一性验证时异常！[" + value + "]<br/>" + e.getMessage());
						}
						if (size > 0) {
							if (column.isIgnore()) {
								return;
							}
							throw new RuntimeException("第[ " + rowIndex + " ]行 “" + titleMap.get(i) + "” 在记录已存在，此值必须唯一！");
						}
					}
					
					sql.append(",").append(column.getName());
					sqlValue.append(",?");
					values.add(value);
					
					if (isProduct && "VALUE".equals(column.getName().toUpperCase())) {
						idValue = (String)value;
					}
				} else if (column.isNotNull()) {
					throw new RuntimeException("第[ " + rowIndex + " ]行 “" + titleMap.get(i) + "” 值 “" + colValue + "” 在选项中未找到！");
				}
			} else if (column.isNotNull()) {
				throw new RuntimeException("第[ " + rowIndex + " ]行 “" + titleMap.get(i) + "” 不能为空！");
			}
		}
		if (codes != null) {
			for (String name : codes.keySet()) {
				sql.append(",").append(name);
				sqlValue.append(",?");
				values.add(codes.get(name));
			}
		}
		
		if (!idColumn) {
			sql.append(",").append(idName);
			sqlValue.append(",'").append(idValue).append("'");
		}
		if (!orgColumn) {
			sql.append(",Ad_Org_Id");
			sqlValue.append(",'").append(orgId).append("'");
		}
		if (!activeColumn) {
			sql.append(",isActive");
			sqlValue.append(",'Y'");
		}
		
		sql.append(sqlValue).append(")");
		Query query = OBDal.getInstance().getSession().createSQLQuery(sql.toString());
		for (int i = 0; i < values.size(); i++) {
			query.setParameter(i, values.get(i));
		}
		query.executeUpdate();
		OBDal.getInstance().flush();
	}
	
	/**
	 * 根据数据类型获取数值
	 */
	protected Object getDataValue(Object value, DataColumn column, Map<String, Object> valueMap) throws Exception {
		if (column.getWhereSql() != null) {
			String sql = column.getWhereSql();
			for (Object key : valueMap.keySet()) {
				sql = sql.replace("{" + key + "}", "'" + valueMap.get(key) + "'");
			}
			try {
				if (sql.indexOf("?") > -1) {
					if (value == null || "".equals(value)) {
						return null;
					}
					return getSqlValue(sql, value.toString());
				} else {
					return getSqlValue(sql);
				}
			} catch (Exception e) {
				if (e.getCause() != null) {
					throw new RuntimeException("查询关联数据时异常！[" + value + "]<br/>" + e.getCause().getMessage());
				}
				throw new RuntimeException("查询关联数据时异常！[" + value + "]<br/>" + e.getMessage());
			}
		} else if ("11".equals(column.getTypeId())) {//整数
			Double d = Double.valueOf(value + "");
			return d.longValue();
		} else if ("12".equals(column.getTypeId()) || "29".equals(column.getTypeId())) {//数量
			return BigDecimal.valueOf(Double.valueOf(value + ""));
		} else if ("20".equals(column.getTypeId())) {//是否
			return "是".equals(value) || "Y".equals((value + "").toUpperCase()) ? "Y" : "N";
		} else if ("15".equals(column.getTypeId()) || "16".equals(column.getTypeId())) {//日期，日期时间
			if (value instanceof Date) {
				return value;
			}
			return sdf.parse(value + "");
		} else {
			if (value instanceof Date) {
				value = dateSdf.format((Date)value);
			} else {
				value = value + "";
			}
			if ("17".equals(column.getTypeId())) {//列表
				return getListValue(column, value);
			} else if ("18".equals(column.getTypeId())) {//表
				return getTableValue(column, value);
			} else if ("19".equals(column.getTypeId())) {//直接访问表
				return getLinkTableValue(column, value);
			} else if ("30".equals(column.getTypeId())) {//搜索
				return getSearchTableValue(column, value);
			}
		}
		return value;
	}
	
	protected Object getSqlValue(String sql, Object... value) {
		Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
		if (value != null) {
			for (int i = 0; i < value.length; i++) {
				query.setParameter(0, value[i]);
			}
		}
		return query.uniqueResult();
	}
	
	/**
	 * 查询列表中值
	 */
	protected Object getListValue(DataColumn column, Object value) {
		String sql = "select value from AD_Ref_List where name = ? and ad_reference_id = ?";
		Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
		query.setParameter(0, value);
		query.setParameter(1, column.getReferenceId());
		return query.uniqueResult();
	}
	/**
	 * 查询表引用中值
	 */
	protected Object getTableValue(DataColumn column, Object value) {
		String sql = column.getWhereSql();
		if (sql == null) {
			sql = "select (select t.tablename from ad_table t where t.ad_table_id = r.ad_table_id) tableName," +
					"(select c.columnname from ad_column c where c.ad_column_id = r.ad_key) key_Id," +
					"(select c.columnname from ad_column c where c.ad_column_id = r.ad_Display) display_Id " +
					"from AD_Ref_Table r where ad_reference_id = ?";
			
			Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
			query.setParameter(0, column.getReferenceId());
			
			Object [] objs = (Object[])query.uniqueResult();
			sql = "select " + objs[1] + " from " + objs[0] + " t where t." + objs[2] + " = ?";
			column.setWhereSql(sql);
		}
		try {
			return getSqlValue(sql, value);
		} catch (Exception e) {
			throw new RuntimeException("表关联引用查询时异常！[" + value + "]<br/>" + e.getMessage());
		}
	}
	/**
	 * 查询直接访问表中值
	 */
	protected Object getLinkTableValue(DataColumn column, Object... value) {
		try {
			String sql = column.getWhereSql();
			if (sql == null) {
				String tableName = column.getName().substring(0, column.getName().length() - 3);
				//先查询显示字段
				sql = "select c.columnname from ad_column c, ad_table t " +
						"where c.ad_table_id = t.ad_table_id and c.isidentifier = 'Y' and upper(t.tablename) = upper(?)";
				String columnName = (String)getSqlValue(sql, tableName);
				
				sql = "select " + column.getName() + " from " + tableName + " t where t." + columnName + " = ?";
				column.setWhereSql(sql);
			}
			return getSqlValue(sql, value);
		} catch (Exception e) {
			throw new RuntimeException("直接访问表引用查询时异常！[" + value + "]<br/>" + e.getMessage());
		}
	}
	
	/**
	 * 查询搜索选项数据值
	 */
	protected Object getSearchTableValue(DataColumn column, Object value) {
		String sql = column.getWhereHql();
		if (sql == null) {
			sql = "select t.tablename, t.name," +
					"(select c.columnname from ad_column c where c.ad_column_id = s.ad_column_id) valueId," +
					"(select sf.display_expression from Obuisel_Selector_Field sf " +
					"  where sf.obuisel_selector_field_id = s.displayfield_id) hqlName," +
					"(select sf.property from Obuisel_Selector_Field sf " +
					"  where sf.obuisel_selector_field_id = s.displayfield_id) hqlColumn " +
					" from ad_table t, OBUISEL_Selector s " +
					"where t.ad_table_id = s.ad_table_id " +
					"  and s.ad_reference_id = ?";
			Query query = OBDal.getInstance().getSession().createSQLQuery(sql);
			query.setParameter(0, column.getReferenceId());
			String[] strs = (String[])query.uniqueResult();
			
			if (strs[3] != null) {
				sql = "select e.id from " + strs[0] + " e where e." + strs[3] + " = ?";
				column.setWhereHql(sql);
			} else if (strs[4] != null) {
				sql = "select e.id from " + strs[0] + " e where e." + strs[4] + " = ?";
				column.setWhereHql(sql);
			} else {
				return getLinkTableValue(column, value);
			}
		}
		Query query = OBDal.getInstance().getSession().createQuery(sql);
		query.setParameter(0, value);
		return query.uniqueResult();
	}
	
	/**
	 * 读取单元格数据
	 */
	protected Object readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Double cellValue = null;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			cellValue = cell.getNumericCellValue();
			if (cellValue.longValue() < cellValue) {
				return cellValue;
			}
			long value = cellValue.longValue();
			return value;
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new RuntimeException("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}